package com.manager.service.news;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.List;

import org.common.util.TranslateHelper;
import org.jorm.Session;

import com.build.SessionBuilder;
import com.web.model.Category;
import com.web.model.Message;
import com.web.model.dao.CategoryDao;
import com.web.model.dao.impl.CategoryDaoImpl;

public class CategoryService {

	public List<Category> findList() {
		List<Category> list = null;
		try {
			Session session = SessionBuilder.oracleSession();
			CategoryDao categoryDao = new CategoryDaoImpl();
			String sql = "select  * from " + Category.TABLE_NAME + " order by "
					+ Category.COLUMN_ORDER_NO + " ";
			list = categoryDao.queryForList(sql);

			session.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	public List<Category> findShow() {
		List<Category> list = null;
		try {
			Session session = SessionBuilder.oracleSession();
			CategoryDao categoryDao = new CategoryDaoImpl();
			String sql = "select  * from " + Category.TABLE_NAME + " where  "
					+ Category.COLUMN_STATE + "=0  order by "
					+ Category.COLUMN_ORDER_NO + " ";
			list = categoryDao.queryForList(sql);

			session.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	public int save(Category category) {
		CategoryDao categoryDao = new CategoryDaoImpl();
		int id = 0;
		try {
			
			Session session = SessionBuilder.oracleSession();
			Connection conn = session.connection();
		
			PreparedStatement ps =null;
			ResultSet rs = null;
			if(category.getId()==null||category.getId()==0){
				 
					String sqlMax = " select max("+Category.COLUMN_ID+")+1 from   "+Category.TABLE_NAME;
					ps = conn.prepareStatement(sqlMax);
					rs = ps.executeQuery();
					
					while (rs.next())
						id = rs.getInt(1);
					category.setId(id);
			} 
			String sql = "insert into tw_category(id,name,addtime,pid,des,order_no,state,bgurl,types,url)   "
				+" values (?,?,sysdate,?,?,?,?,?,?,?)";
			ps = conn.prepareStatement(sql);

			ps.setInt(1, category.getId());
			ps.setString(2,category.getName()); 
			ps.setInt(3, category.getPid());
			ps.setString(4,category.getDes());
			ps.setInt(5,TranslateHelper.parseInteger(category.getOrderNo())); 
			ps.setInt(6,TranslateHelper.parseInteger(category.getState())); 
			ps.setString(7,category.getBgurl());
			ps.setInt(8,TranslateHelper.parseInteger(category.getTypes())); 
			ps.setString(9,category.getUrl());
			 
			ps.executeUpdate(); 
			ps.close();
			session.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return id;

	}
	public int update(Category category) {
		CategoryDao categoryDao = new CategoryDaoImpl();
		int id = 0;
		try {
			
			Session session = SessionBuilder.oracleSession();
			Connection conn = session.connection(); 
			PreparedStatement ps =null;
			ResultSet rs = null;
			 
			String sql = "update tw_category set name=?,pid=? , des=? , order_no=?, " +
					"state=? , bgurl=?, types=?, url=? where id=?";
			ps = conn.prepareStatement(sql);
			
			ps.setString(1,category.getName()); 
			ps.setInt(2, category.getPid()); 
			ps.setString(3,category.getDes()); 
			ps.setInt(4,TranslateHelper.parseInteger(category.getOrderNo()));
			ps.setInt(5,TranslateHelper.parseInteger(category.getState()));
			ps.setString(6,category.getBgurl()); 
			ps.setInt(7,TranslateHelper.parseInteger(category.getTypes())); 
			ps.setString(8,category.getUrl());
			
			ps.setInt(9, category.getId());
			ps.executeUpdate(); 
			  
			ps.close();
			session.close();
			id=0;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return id;

	}
	public Category getById(int id) {
		Category obj = new Category();
		try {
			Session session = SessionBuilder.oracleSession();
			Connection conn = session.connection();
			String sql = "select * from " + Category.TABLE_NAME + " where "
					+ Category.COLUMN_ID + " = ?";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				obj = new Category();
				obj.setAddtime(rs.getTimestamp(Category.COLUMN_ADDTIME));
				obj.setId(rs.getInt(Category.COLUMN_ID));
				obj.setUrl(rs.getString(Category.COLUMN_URL));
				obj.setBgurl(rs.getString(Category.COLUMN_BGURL));
				obj.setDes(rs.getString(Category.COLUMN_DES));
				obj.setName(rs.getString(Category.COLUMN_NAME));
				obj.setOrderNo(rs.getInt(Category.COLUMN_ORDER_NO));
				obj.setPid(rs.getInt(Category.COLUMN_PID));
				obj.setState(rs.getInt(Category.COLUMN_STATE));
				obj.setTypes(rs.getInt(Category.COLUMN_TYPES));
			}
			rs.close();
			ps.close();
			session.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return obj;
	}
	public void delete(int id) {
		try {
			Session session = SessionBuilder.oracleSession();
			String sql = "delete from " +  Category.TABLE_NAME + " where " + Category.COLUMN_ID + " = ?";
			Connection conn = session.connection();
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			ps.executeUpdate();
			ps.close();
			session.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

}
